package com.jieshen.JDBC;

import com.jieshen.JDBC.domain.Emp;
import com.jieshen.JDBC.utils.JDBCUtils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class DQLdemo {

    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());


    /**
     * queryForMap
     * 注意：这个方法查询的结果集长度只能是1
     */
    @Test
    public void queryForMap() {
        String sql = "select * from emp where id = ?";
        Map<String, Object> stringObjectMap = template.queryForMap(sql, 2);
        System.out.println(stringObjectMap);
    }

    @Test
    public void queryForList() {
        String sql = "select * from emp";
        List<Map<String, Object>> maps = template.queryForList(sql);
        for (Map<String, Object> map : maps) {
            System.out.println(map);
        }
    }

    /**
     *
     */
    @Test
    public void queryClass(){
        String sql = "select * from emp";
        List<Emp> query = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));
        for (Emp emp : query) {
            System.out.println(emp);
        }
    }

    /**
     * 仅供学习，该代码并没有简化编程
     * 自己实现将查询到的数据封装在class中的过程
     */
    @Test
    public void queryClassLearn() {
        String sql = "select * from emp";
        List<Emp> list = template.query(sql, new RowMapper<Emp>() {

            @Override
            public Emp mapRow(ResultSet resultSet, int i) throws SQLException {
                Emp emp = new Emp();
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                emp.setId(id);
                emp.setName(name);
                emp.setEmail(email);
                return emp;
            }
        });
        for (Emp emp : list) {
            System.out.println(emp);
        }
    }

    @Test
    public void queryForObject(){
        String sql = "select count(id) from emp";
        Integer integer = template.queryForObject(sql, int.class);
        System.out.println(integer);
    }
}
